OML4SQL Classification NN.dsnb

[{"layout":null,"template":null,"templateConfig":null,"name":"OML4SQL Classification NN","description":null,"readOnly":false,"type":"low","paragraphs":[{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":null,"message":[],"enabled":true,"result":{"startTime":1715349893912,"interpreter":"md.low","endTime":1715349894068,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","# Classification Modeling to Predict Target Customers using Neural Network","In this notebook, we predict customers most likely to be positive responders to an Affinity Card loyalty program. High Affinity Card responders (target value = 1) are defined as those customers who when given a loyaly or affinity card hyper-respond i.e. they increae their purchasing higher than the Affinity Card program's offered discount percentage. This notebook builds and applies classification neural network models using the SH schema data. All processing occurs inside Oracle Autonomous Database.","","Copyright (c) 2024 Oracle Corporation ","###### <a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a>","---"],"enabled":true,"result":{"startTime":1715349894151,"interpreter":"md.low","endTime":1715349894246,"results":[{"message":"<h1 id=\"classification-modeling-to-predict-target-customers-using-neural-network\">Classification Modeling to Predict Target Customers using Neural Network<\/h1>\n<p>In this notebook, we predict customers most likely to be positive responders to an Affinity Card loyalty program. High Affinity Card responders (target value = 1) are defined as those customers who when given a loyaly or affinity card hyper-respond i.e. they increae their purchasing higher than the Affinity Card program's offered discount percentage. This notebook builds and applies classification neural network models using the SH schema data. All processing occurs inside Oracle Autonomous Database.<\/p>\n<p>Copyright (c) 2024 Oracle Corporation<\/p>\n<h6 id=\"the-universal-permissive-license-upl-version-10\"><a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a><\/h6>\n<hr />\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":9,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","<dl>","<img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/classification-5663162.jpg\" alt=\"OML Notebooks\" width=\"250\"/>","<\/dl>"],"enabled":true,"result":{"startTime":1715349894329,"interpreter":"md.low","endTime":1715349894406,"results":[{"message":"<dl>\n<img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/classification-5663162.jpg\" alt=\"OML Notebooks\" width=\"250\"/>\n<\/dl>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":3,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"For more information...","message":["%md","","* <a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a>","* <a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a>","* <a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a>","* <a href=\"https://www.oracle.com/goto/ml-classification\" target=\"_blank\">OML Classification<\/a>","* <a href=\"https://oracle.com/goto/ml-neural-network\" target=\"_blank\">OML Neural Network<\/a>","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-7793F608-2719-45EA-87F9-6F246BA800D4\" target=\"_blank\">Algorithm Settings<\/a>","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" target=\"_blank\">Shared Settings<\/a>"],"enabled":true,"result":{"startTime":1715349905298,"interpreter":"md.low","endTime":1715349905399,"results":[{"message":"<ul>\n<li><a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a><\/li>\n<li><a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a><\/li>\n<li><a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a><\/li>\n<li><a href=\"https://www.oracle.com/goto/ml-classification\" target=\"_blank\">OML Classification<\/a><\/li>\n<li><a href=\"https://oracle.com/goto/ml-neural-network\" target=\"_blank\">OML Neural Network<\/a><\/li>\n<li><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-7793F608-2719-45EA-87F9-6F246BA800D4\" target=\"_blank\">Algorithm Settings<\/a><\/li>\n<li><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" target=\"_blank\">Shared Settings<\/a><\/li>\n<\/ul>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display a few rows of the demographics data ","message":["%sql","","SELECT * ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715349894641,"interpreter":"sql.low","endTime":1715349896240,"results":[{"message":"CUST_ID\tEDUCATION\tOCCUPATION\tHOUSEHOLD_SIZE\tYRS_RESIDENCE\tAFFINITY_CARD\tBULK_PACK_DISKETTES\tFLAT_PANEL_MONITOR\tHOME_THEATER_PACKAGE\tBOOKKEEPING_APPLICATION\tPRINTER_SUPPLIES\tY_BOX_GAMES\tOS_DOC_SET_KANJI\tCOMMENTS\n102547\t10th\tOther\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n101050\t10th\tOther\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n100040\t11th\tSales\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n102117\tHS-grad\tFarming\t1\t0\t0\t0\t0\t0\t1\t1\t1\t0\t\n101074\t10th\tHandler\t1\t1\t0\t1\t1\t0\t0\t1\t1\t0\t\n104179\t10th\tHandler\t1\t1\t0\t1\t1\t0\t0\t1\t1\t0\t\n100417\t11th\tHandler\t1\t1\t0\t0\t0\t0\t0\t1\t1\t0\t\n101146\t< Bach.\t?\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n103420\t< Bach.\t?\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n101987\t< Bach.\tOther\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Count number of records in demographics table","message":["%sql","","SELECT COUNT(*) FROM SH.SUPPLEMENTARY_DEMOGRAPHICS;"],"enabled":true,"result":{"startTime":1715349896323,"interpreter":"sql.low","endTime":1715349896418,"results":[{"message":"COUNT(*)\n4500\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"bar\":{\"showSeries\":[\"COUNT\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"AFFINITY_CARD\",\"COUNT\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Show distribution of AFFINITY_CARD responders","message":["%sql","","-- In this data set, hyper-responders to the Affinity Card loyalty program are represented as 1's. ","","SELECT AFFINITY_CARD, COUNT(*) COUNT ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS","GROUP BY AFFINITY_CARD;"],"enabled":true,"result":{"startTime":1715349896495,"interpreter":"sql.low","endTime":1715349896766,"results":[{"message":"AFFINITY_CARD\tCOUNT\n1\t1072\n0\t3428\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"bar\":{\"groupByColumns\":[\"AFFINITY_CARD\",\"HOUSEHOLD_SIZE\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"NUM_CUSTOMERS\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"NUM_CUSTOMERS\",\"HOUSEHOLD_SIZE\",\"AFFINITY_CARD\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Graph HOUSEHOLD_SIZE grouped by AFFINITY_CARD","message":["%sql","","SELECT COUNT(CUST_ID) AS NUM_CUSTOMERS, HOUSEHOLD_SIZE, AFFINITY_CARD ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS ","GROUP BY HOUSEHOLD_SIZE, AFFINITY_CARD","ORDER BY HOUSEHOLD_SIZE, AFFINITY_CARD"],"enabled":true,"result":{"startTime":1715349896845,"interpreter":"sql.low","endTime":1715349896921,"results":[{"message":"NUM_CUSTOMERS\tHOUSEHOLD_SIZE\tAFFINITY_CARD\n681\t1\t0\n11\t1\t1\n1040\t2\t0\n109\t2\t1\n973\t3\t0\n814\t3\t1\n112\t4-5\t0\n107\t4-5\t1\n146\t6-8\t0\n2\t6-8\t1\n476\t9+\t0\n29\t9+\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"bar\":{\"showSeries\":[\"AFFINITY_1_COUNT\",\"AFFINITY_0_COUNT\"],\"aggregationOption\":\"Last\",\"visualizations\":{\"stackValue\":\"on\"},\"series\":{\"availableSeriesElements\":[{\"id\":\"AFFINITY_1_COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0},{\"id\":\"AFFINITY_0_COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(50, 146, 94)\",\"borderWidth\":0,\"color\":\"rgb(50, 146, 94)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(50, 146, 94)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"HOUSEHOLD_SIZE\",\"AFFINITY_1_COUNT\",\"AFFINITY_0_COUNT\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Graph HOUSEHOLD_SIZE grouped by AFFINITY_CARD","message":["%sql","","SELECT HOUSEHOLD_SIZE,"," SUM(CASE WHEN AFFINITY_CARD = 1 THEN NUM_CUSTOMERS ELSE 0 END) AS AFFINITY_1_COUNT,"," SUM(CASE WHEN AFFINITY_CARD = 0 THEN NUM_CUSTOMERS ELSE 0 END) AS AFFINITY_0_COUNT","FROM (SELECT COUNT(CUST_ID) AS NUM_CUSTOMERS, HOUSEHOLD_SIZE, AFFINITY_CARD "," FROM SH.SUPPLEMENTARY_DEMOGRAPHICS "," GROUP BY HOUSEHOLD_SIZE, AFFINITY_CARD)","GROUP BY HOUSEHOLD_SIZE","ORDER BY HOUSEHOLD_SIZE"],"enabled":true,"result":{"startTime":1715349897004,"interpreter":"sql.low","endTime":1715349897081,"results":[{"message":"HOUSEHOLD_SIZE\tAFFINITY_1_COUNT\tAFFINITY_0_COUNT\n1\t11\t681\n2\t109\t1040\n3\t814\t973\n4-5\t107\t112\n6-8\t2\t146\n9+\t29\t476\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create view with selected demographics columns","message":["%script","","CREATE OR REPLACE VIEW DEMOGRAPHICS4_V "," AS SELECT AFFINITY_CARD, BOOKKEEPING_APPLICATION,BULK_PACK_DISKETTES, CUST_ID, EDUCATION,"," FLAT_PANEL_MONITOR, HOME_THEATER_PACKAGE,HOUSEHOLD_SIZE, OCCUPATION, OS_DOC_SET_KANJI,"," PRINTER_SUPPLIES, YRS_RESIDENCE, Y_BOX_GAMES"," FROM SH.SUPPLEMENTARY_DEMOGRAPHICS;"," "],"enabled":true,"result":{"startTime":1715349897159,"interpreter":"script.low","endTime":1715349897536,"results":[{"message":"\nView DEMOGRAPHICS4_V created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display data from DEMOGRAPHICS4_V view","message":["%sql ","","SELECT * ","FROM DEMOGRAPHICS4_V","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715349897630,"interpreter":"sql.low","endTime":1715349897726,"results":[{"message":"AFFINITY_CARD\tBOOKKEEPING_APPLICATION\tBULK_PACK_DISKETTES\tCUST_ID\tEDUCATION\tFLAT_PANEL_MONITOR\tHOME_THEATER_PACKAGE\tHOUSEHOLD_SIZE\tOCCUPATION\tOS_DOC_SET_KANJI\tPRINTER_SUPPLIES\tYRS_RESIDENCE\tY_BOX_GAMES\n0\t0\t1\t102547\t10th\t1\t0\t1\tOther\t0\t1\t0\t1\n0\t0\t1\t101050\t10th\t1\t0\t1\tOther\t0\t1\t0\t1\n0\t0\t1\t100040\t11th\t1\t0\t1\tSales\t0\t1\t0\t1\n0\t1\t0\t102117\tHS-grad\t0\t0\t1\tFarming\t0\t1\t0\t1\n0\t0\t1\t101074\t10th\t1\t0\t1\tHandler\t0\t1\t1\t1\n0\t0\t1\t104179\t10th\t1\t0\t1\tHandler\t0\t1\t1\t1\n0\t0\t0\t100417\t11th\t0\t0\t1\tHandler\t0\t1\t1\t1\n0\t1\t1\t101146\t< Bach.\t1\t0\t1\t?\t0\t1\t1\t1\n0\t1\t1\t103420\t< Bach.\t1\t0\t1\t?\t0\t1\t1\t1\n0\t1\t1\t101987\t< Bach.\t1\t0\t1\tOther\t0\t1\t1\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create train and test data sets (60/40) for model build and test","message":["%script","","CREATE OR REPLACE VIEW TRAIN_DATA_CLAS AS SELECT * FROM DEMOGRAPHICS4_V SAMPLE (60) SEED (1);"," ","CREATE OR REPLACE VIEW TEST_DATA_CLAS AS "," SELECT * FROM DEMOGRAPHICS4_V "," MINUS "," SELECT * FROM TRAIN_DATA_CLAS;"],"enabled":true,"result":{"startTime":1715349897807,"interpreter":"script.low","endTime":1715349897934,"results":[{"message":"\nView TRAIN_DATA_CLAS created.\n\n\n---------------------------\n\nView TEST_DATA_CLAS created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Build a Neural Network classification model for predicting AFFINITY_CARD","","---"],"enabled":true,"result":{"startTime":1715349898018,"interpreter":"md.low","endTime":1715349898081,"results":[{"message":"<h3 id=\"build-a-neural-network-classification-model-for-predicting-affinity_card\">Build a Neural Network classification model for predicting AFFINITY_CARD<\/h3>\n<hr />\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a NN model using default settings","message":["%script","","","BEGIN DBMS_DATA_MINING.DROP_MODEL('NN_CLASS_MODEL');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_NEURAL_NETWORK';",""," DBMS_DATA_MINING.CREATE_MODEL2("," MODEL_NAME => 'NN_CLASS_MODEL',"," MINING_FUNCTION => 'CLASSIFICATION',"," DATA_QUERY => 'SELECT * FROM TRAIN_DATA_CLAS',"," SET_LIST => v_setlst,"," CASE_ID_COLUMN_NAME => 'CUST_ID',"," TARGET_COLUMN_NAME => 'AFFINITY_CARD'"," );"," ","END;"],"enabled":true,"result":{"startTime":1715349898157,"interpreter":"script.low","endTime":1715349904060,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":8,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"html\":{\"height\":400,\"lastColumns\":[]}}]","hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Examples of possible setting overrides for Neural Network","","If the user does not override the default settings, then relevant settings are determined by the algorithm.","","A complete list of settings can be found in the Documentation link:","","- Algorithm Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-7793F608-2719-45EA-87F9-6F246BA800D4\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-7793F608-2719-45EA-87F9-6F246BA800D4');\">Neural Network<\/a>","- Shared Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75');\">All algorithms<\/a>","- Specify a row weight column ","> v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '<row_weight_column_name>';"," ","* Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is `ODMS_MISSING_VALUE_AUTO`. The option `ODMS_MISSING_VALUE_MEAN_MODE` replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option `ODMS_MISSING_VALUE_AUTO` performs different strategies for different algorithms. When `ODMS_MISSING_VALUE_TREATMENT` is set to `ODMS_MISSING_VALUE_DELETE_ROW`, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.","> v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';","* Specify the Neural Network architecture. The default number of layers is 1, and the default activation function is NNET_ACTIVATIONS_LOG_SIG (Log Sigmoid). The default number of nodes per layer is the number of attributes or 50 (if the number of attributes > 50). Be careful with quotes around NNET_ACTIVATIONS. The options for the activation functions are one or more of the following strings: `NNET_ACTIVATIONS_ARCTAN`, `NNET_ACTIVATIONS_BIPOLAR_SIG`, `NNET_ACTIVATIONS_LINEAR`, `NNET_ACTIVATIONS_LOG_SIG`, `NNET_ACTIVATIONS_TANH` and <strong>starting from Oracle Database 23ai<\/strong> `NNET_ACTIVATIONS_RELU`.","> v_setlst('NNET_HIDDEN_LAYERS') := '3';","","> v_setlst('NNET_NODES_PER_LAYER') := '8,8,2';","","> v_setlst('NNET_ACTIVATIONS') := '''NNET_ACTIVATIONS_TANH'', ''NNET_ACTIVATIONS_LOG_SIG'', ''NNET_ACTIVATIONS_ARCTAN''';"," ","* Specify the Heldaside options for Regularization. If the total number of training rows is greater than 50000, the default is NNET_REGULARIZER_HELDASIDE, otherwise it is NNET_REGULARIZER_NONE. The held ratio for the held-aside method is 0.25 by default. With NNET_REGULARIZER_HELDASIDE, the training process is stopped early if the network performance on the validation data fails to improve for NNET_HELDASIDE_MAX_FAIL epochs in a row (default is 6).","> v_setlst('NNET_REGULARIZER') := 'NNET_REGULARIZER_HELDASIDE';","","> v_setlst('NNET_HELDASIDE_RATIO') := '0.25';","","> v_setlst('NNET_HELDASIDE_MAX_FAIL') := '6';"," ","* Specify the L2 regularization parameter, with a default of 1. Cannot be used with NNET_REGULARIZER_HELDASIDE.","> v_setlst('NNET_REG_LAMBDA') := '1';","* Specify the number of iterations, which is 200 by default for LBFGS solver and 10000 for the ADAM solver. The default convergence tolerance setting is 0.000001. ","> v_setlst('NNET_ITERATIONS') := '200';","","> v_setlst('NNET_TOLERANCE') := '0.000001';","","> v_setlst('ODMS_RANDOM_SEED') := '0';","* Specify details on the LBFGS Solver. The number of historical copies kept in L-BFGS solver is defined by LBFGS_HISTORY_DEPTH. When the number of iterations is smaller than the history depth, the Hessian computed by L-BFGS is accurate, otherwise the Hessian computed by L-BFGS is an approximation. Typically, the value is between 3 and 10. "," v_setlst('LBFGS_HISTORY_DEPTH') := '10';","* Specify the gradient infinity norm tolerance for L-BFGS. The default value is 1E-9. ","> v_setlst('LBFGS_GRADIENT_TOLERANCE') := '0.0000001';","* Specify how to set the initial approximation of the inverse Hessian at the beginning of each iteration. If the value is set to be LBFGS_SCALE_HESSIAN_ENABLE, then we approximate the initial inverse Hessian with Oren-Luenberger scaling. If it is set to be LBFGS_SCALE_HESSIAN_DISABLE, then we use identity as the approximation of the inverse Hessian at the beginning of each iteration ","> v_setlst('LBFGS_SCALE_HESSIAN') := 'LBFGS_SCALE_HESSIAN_ENABLE';","","> v_setlst('LBFGS_SCALE_HESSIAN') := 'LBFGS_SCALE_HESSIAN_DISABLE';","","* Specify the lower bound of the region where weights are randomly initialized. `NNET_WEIGHT_LOWER_BOUND` and `NNET_WEIGHT_UPPER_BOUND` must be set together. Setting one and not setting the other raises an error. `NNET_WEIGHT_LOWER_BOUND` must not be greater than `NNET_WEIGHT_UPPER_BOUND`. The default value is -sqrt(6/(l_nodes+r_nodes)). "," The value of l_nodes for `input layer dense attributes` is (1+number of dense attributes), `input layer sparse attributes` is number of sparse attributes, and `each hidden layer` is (1+number of nodes in that hidden layer). The value of r_nodes is the number of nodes in the layer that the weight is connecting to."," If not specified, the values of `NNET_WEIGHT_LOWER_BOUND` and `NNET_WEIGHT_UPPER_BOUND` are system determined.","> v_setlst('NNET_WEIGHT_LOWER_BOUND') := 'a real number'","> v_setlst('NNET_WEIGHT_UPPER_BOUND') := 'a real number'"," ","```"],"enabled":true,"result":{"startTime":1715349904150,"interpreter":"md.low","endTime":1715349904265,"results":[{"message":"<h3 id=\"examples-of-possible-setting-overrides-for-neural-network\">Examples of possible setting overrides for Neural Network<\/h3>\n<p>If the user does not override the default settings, then relevant settings are determined by the algorithm.<\/p>\n<p>A complete list of settings can be found in the Documentation link:<\/p>\n<ul>\n<li>Algorithm Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-7793F608-2719-45EA-87F9-6F246BA800D4\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-7793F608-2719-45EA-87F9-6F246BA800D4');\">Neural Network<\/a><\/li>\n<li>Shared Settings: <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" onclick=\"return ! window.open('https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75');\">All algorithms<\/a><\/li>\n<li>Specify a row weight column<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '&lt;row_weight_column_name&gt;';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is <code>ODMS_MISSING_VALUE_AUTO<\/code>. The option <code>ODMS_MISSING_VALUE_MEAN_MODE<\/code> replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option <code>ODMS_MISSING_VALUE_AUTO<\/code> performs different strategies for different algorithms. When <code>ODMS_MISSING_VALUE_TREATMENT<\/code> is set to <code>ODMS_MISSING_VALUE_DELETE_ROW<\/code>, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the Neural Network architecture. The default number of layers is 1, and the default activation function is NNET_ACTIVATIONS_LOG_SIG (Log Sigmoid). The default number of nodes per layer is the number of attributes or 50 (if the number of attributes &gt; 50). Be careful with quotes around NNET_ACTIVATIONS. The options for the activation functions are one or more of the following strings: <code>NNET_ACTIVATIONS_ARCTAN<\/code>, <code>NNET_ACTIVATIONS_BIPOLAR_SIG<\/code>, <code>NNET_ACTIVATIONS_LINEAR<\/code>, <code>NNET_ACTIVATIONS_LOG_SIG<\/code>, <code>NNET_ACTIVATIONS_TANH<\/code> and <strong>starting from Oracle Database 23ai<\/strong> <code>NNET_ACTIVATIONS_RELU<\/code>.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('NNET_HIDDEN_LAYERS') := '3';<\/p>\n<\/blockquote>\n<blockquote>\n<p>v_setlst('NNET_NODES_PER_LAYER') := '8,8,2';<\/p>\n<\/blockquote>\n<blockquote>\n<p>v_setlst('NNET_ACTIVATIONS') := '''NNET_ACTIVATIONS_TANH'', ''NNET_ACTIVATIONS_LOG_SIG'', ''NNET_ACTIVATIONS_ARCTAN''';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the Heldaside options for Regularization. If the total number of training rows is greater than 50000, the default is NNET_REGULARIZER_HELDASIDE, otherwise it is NNET_REGULARIZER_NONE. The held ratio for the held-aside method is 0.25 by default. With NNET_REGULARIZER_HELDASIDE, the training process is stopped early if the network performance on the validation data fails to improve for NNET_HELDASIDE_MAX_FAIL epochs in a row (default is 6).<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('NNET_REGULARIZER') := 'NNET_REGULARIZER_HELDASIDE';<\/p>\n<\/blockquote>\n<blockquote>\n<p>v_setlst('NNET_HELDASIDE_RATIO') := '0.25';<\/p>\n<\/blockquote>\n<blockquote>\n<p>v_setlst('NNET_HELDASIDE_MAX_FAIL') := '6';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the L2 regularization parameter, with a default of 1. Cannot be used with NNET_REGULARIZER_HELDASIDE.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('NNET_REG_LAMBDA') := '1';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the number of iterations, which is 200 by default for LBFGS solver and 10000 for the ADAM solver. The default convergence tolerance setting is 0.000001.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('NNET_ITERATIONS') := '200';<\/p>\n<\/blockquote>\n<blockquote>\n<p>v_setlst('NNET_TOLERANCE') := '0.000001';<\/p>\n<\/blockquote>\n<blockquote>\n<p>v_setlst('ODMS_RANDOM_SEED') := '0';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify details on the LBFGS Solver. The number of historical copies kept in L-BFGS solver is defined by LBFGS_HISTORY_DEPTH. When the number of iterations is smaller than the history depth, the Hessian computed by L-BFGS is accurate, otherwise the Hessian computed by L-BFGS is an approximation. Typically, the value is between 3 and 10.\nv_setlst('LBFGS_HISTORY_DEPTH') := '10';<\/li>\n<li>Specify the gradient infinity norm tolerance for L-BFGS. The default value is 1E-9.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('LBFGS_GRADIENT_TOLERANCE') := '0.0000001';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify how to set the initial approximation of the inverse Hessian at the beginning of each iteration. If the value is set to be LBFGS_SCALE_HESSIAN_ENABLE, then we approximate the initial inverse Hessian with Oren-Luenberger scaling. If it is set to be LBFGS_SCALE_HESSIAN_DISABLE, then we use identity as the approximation of the inverse Hessian at the beginning of each iteration<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('LBFGS_SCALE_HESSIAN') := 'LBFGS_SCALE_HESSIAN_ENABLE';<\/p>\n<\/blockquote>\n<blockquote>\n<p>v_setlst('LBFGS_SCALE_HESSIAN') := 'LBFGS_SCALE_HESSIAN_DISABLE';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the lower bound of the region where weights are randomly initialized. <code>NNET_WEIGHT_LOWER_BOUND<\/code> and <code>NNET_WEIGHT_UPPER_BOUND<\/code> must be set together. Setting one and not setting the other raises an error. <code>NNET_WEIGHT_LOWER_BOUND<\/code> must not be greater than <code>NNET_WEIGHT_UPPER_BOUND<\/code>. The default value is -sqrt(6/(l_nodes+r_nodes)).\nThe value of l_nodes for <code>input layer dense attributes<\/code> is (1+number of dense attributes), <code>input layer sparse attributes<\/code> is number of sparse attributes, and <code>each hidden layer<\/code> is (1+number of nodes in that hidden layer). The value of r_nodes is the number of nodes in the layer that the weight is connecting to.\nIf not specified, the values of <code>NNET_WEIGHT_LOWER_BOUND<\/code> and <code>NNET_WEIGHT_UPPER_BOUND<\/code> are system determined.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('NNET_WEIGHT_LOWER_BOUND') := 'a real number'\nv_setlst('NNET_WEIGHT_UPPER_BOUND') := 'a real number'<\/p>\n<\/blockquote>\n<pre><code><\/code><\/pre>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Use 3 hidden layers and specific activation functions ","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('NN_CLASS_MODEL2');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_NEURAL_NETWORK';"," v_setlst('NNET_NODES_PER_LAYER') := '8,8,2';"," v_setlst('NNET_ACTIVATIONS') := '''NNET_ACTIVATIONS_TANH'', "," ''NNET_ACTIVATIONS_LOG_SIG'', "," ''NNET_ACTIVATIONS_ARCTAN''';"," DBMS_DATA_MINING.CREATE_MODEL2("," MODEL_NAME => 'NN_CLASS_MODEL2',"," MINING_FUNCTION => 'CLASSIFICATION',"," DATA_QUERY => 'SELECT * FROM TRAIN_DATA_CLAS',"," SET_LIST => v_setlst,"," CASE_ID_COLUMN_NAME => 'CUST_ID',"," TARGET_COLUMN_NAME => 'AFFINITY_CARD'"," );"," ","END;"],"enabled":true,"result":{"startTime":1715349904350,"interpreter":"script.low","endTime":1715349907852,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Use held aside regularization and early stopping ","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('NN_CLASS_MODEL3');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_NEURAL_NETWORK';"," v_setlst('NNET_HIDDEN_LAYERS') := '3';"," v_setlst('NNET_NODES_PER_LAYER') := '8,8,2';"," v_setlst('NNET_ACTIVATIONS') := '''NNET_ACTIVATIONS_TANH'', "," ''NNET_ACTIVATIONS_LOG_SIG'', "," ''NNET_ACTIVATIONS_ARCTAN''';"," v_setlst('NNET_REGULARIZER') := 'NNET_REGULARIZER_HELDASIDE';"," v_setlst('NNET_HELDASIDE_RATIO') := '0.25';"," v_setlst('NNET_HELDASIDE_MAX_FAIL') := '6';"," v_setlst('NNET_ITERATIONS') := '200';"," v_setlst('NNET_TOLERANCE') := '0.000001';"," v_setlst('ODMS_RANDOM_SEED') := '0'; "," "," DBMS_DATA_MINING.CREATE_MODEL2("," 'NN_CLASS_MODEL3',"," 'CLASSIFICATION',"," 'SELECT * FROM TRAIN_DATA_CLAS',"," v_setlst,"," 'CUST_ID',"," 'AFFINITY_CARD');","END;"],"enabled":true,"result":{"startTime":1715349907934,"interpreter":"script.low","endTime":1715349909837,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get list of model views","message":["%sql ","","SELECT VIEW_NAME, VIEW_TYPE ","FROM USER_MINING_MODEL_VIEWS","WHERE MODEL_NAME='NN_CLASS_MODEL'","ORDER BY VIEW_NAME;"," "],"enabled":true,"result":{"startTime":1715349909916,"interpreter":"sql.low","endTime":1715349910017,"results":[{"message":"VIEW_NAME\tVIEW_TYPE\nDM$VANN_CLASS_MODEL\tNeural Network Weights\nDM$VCNN_CLASS_MODEL\tScoring Cost Matrix\nDM$VGNN_CLASS_MODEL\tGlobal Name-Value Pairs\nDM$VNNN_CLASS_MODEL\tNormalization and Missing Value Handling\nDM$VSNN_CLASS_MODEL\tComputed Settings\nDM$VTNN_CLASS_MODEL\tClassification Targets\nDM$VWNN_CLASS_MODEL\tModel Build Alerts\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get general settings","message":["%sql","","SELECT NAME, NUMERIC_VALUE, STRING_VALUE from DM$VGNN_CLASS_MODEL;"],"enabled":true,"result":{"startTime":1715349910101,"interpreter":"sql.low","endTime":1715349910202,"results":[{"message":"NAME\tNUMERIC_VALUE\tSTRING_VALUE\nNUM_ROWS\t2679\t\nITERATIONS\t200\t\nLOSS_VALUE\t0.076290427056661489\t\nCONVERGED\t\tNO\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get information for missing value handling","message":["%sql","","SELECT * FROM DM$VNNN_CLASS_MODEL"],"enabled":true,"result":{"startTime":1715349910371,"interpreter":"sql.low","endTime":1715349910473,"results":[{"message":"PARTITION_NAME\tATTRIBUTE_NAME\tATTRIBUTE_SUBNAME\tNUMERIC_MISSING_VALUE\tCATEGORICAL_MISSING_VALUE\tNORMALIZATION_SHIFT\tNORMALIZATION_SCALE\n\tEDUCATION\t\t\tHS-grad\t\t\n\tHOUSEHOLD_SIZE\t\t\t3\t\t\n\tOCCUPATION\t\t\tCrafts\t\t\n\tBOOKKEEPING_APPLICATION\t\t0.8783128032848078\t\t0.8783128032848078\t0.3269852183756386\n\tBULK_PACK_DISKETTES\t\t0.6360582306830922\t\t0.6360582306830922\t0.48122198469071364\n\tFLAT_PANEL_MONITOR\t\t0.5759611795446065\t\t0.5759611795446065\t0.49428847631749057\n\tHOME_THEATER_PACKAGE\t\t0.5591638671145956\t\t0.5591638671145956\t0.49657998650332785\n\tOS_DOC_SET_KANJI\t\t0.0029861888764464357\t\t0.0029861888764464357\t0.054574566460277744\n\tPRINTER_SUPPLIES\t\t1.0\t\t1.0\t1.0\n\tYRS_RESIDENCE\t\t4.001493094438232\t\t4.001493094438232\t1.9178414346193704\n\tY_BOX_GAMES\t\t0.3154162000746549\t\t0.3154162000746549\t0.4647681695563441\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Evaluate the model","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE APPLY_RESULT PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","BEGIN EXECUTE IMMEDIATE 'DROP TABLE LIFT_TABLE PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","BEGIN"," DBMS_DATA_MINING.APPLY('NN_CLASS_MODEL','TEST_DATA_CLAS','CUST_ID','APPLY_RESULT');"," DBMS_DATA_MINING.COMPUTE_LIFT('APPLY_RESULT','TEST_DATA_CLAS','CUST_ID','AFFINITY_CARD',"," 'LIFT_TABLE','1','PREDICTION','PROBABILITY',100);","END;"],"enabled":true,"result":{"startTime":1715349910556,"interpreter":"script.low","endTime":1715349911960,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":7,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"line\":{\"showSeries\":[\"GAIN_CUMULATIVE\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"GAIN_CUMULATIVE\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"QUANTILE_NUMBER\",\"GAIN_CUMULATIVE\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"line","title":"View model's cumulative gains (lift) chart","message":["%sql","","","SELECT QUANTILE_NUMBER, GAIN_CUMULATIVE FROM LIFT_TABLE;"],"enabled":true,"result":{"startTime":1715349912046,"interpreter":"sql.low","endTime":1715349912143,"results":[{"message":"QUANTILE_NUMBER\tGAIN_CUMULATIVE\n1\t3.58302771327007253012048192771084337349E-02\n2\t7.16605542654014506024096385542168674699E-02\n3\t1.13253012048192771084337349397590361446E-01\n4\t1.59036144578313253012048192771084337349E-01\n5\t1.95180722891566265060240963855421686747E-01\n6\t2.19277108433734939759036144578313253012E-01\n7\t2.5542168674698795180722891566265060241E-01\n8\t2.93373493975903614457831325301204819277E-01\n9\t3.30120481927710843373493975903614457831E-01\n10\t3.74698795180722891566265060240963855422E-01\n11\t4.06746995305440506024096385542168674699E-01\n12\t4.30923682523060987951807228915662650602E-01\n13\t4.57831325301204819277108433734939759036E-01\n14\t4.84337349397590361445783132530120481928E-01\n15\t5.10843373493975903614457831325301204819E-01\n16\t5.37349397590361445783132530120481927711E-01\n17\t5.63052221091396843373493975903614457831E-01\n18\t5.85542168674698795180722891566265060241E-01\n19\t6.15662650602409638554216867469879518072E-01\n20\t6.31325301204819277108433734939759036145E-01\n21\t6.53012048192771084337349397590361445783E-01\n22\t6.69879518072289156626506024096385542169E-01\n23\t6.95903585043298192771084337349397590361E-01\n24\t7.17269051793109927710843373493975903615E-01\n25\t7.37349397590361445783132530120481927711E-01\n26\t7.59036144578313253012048192771084337349E-01\n27\t7.81204848691641566265060240963855421687E-01\n28\t8.0361445783132530120481927710843373494E-01\n29\t8.12048192771084337349397590361445783133E-01\n30\t8.24096385542168674698795180722891566265E-01\n31\t8.28915662650602409638554216867469879518E-01\n32\t8.4819277108433734939759036144578313253E-01\n33\t8.53012048192771084337349397590361445783E-01\n34\t8.57831325301204819277108433734939759036E-01\n35\t8.57831325301204819277108433734939759036E-01\n36\t8.65060240963855421686746987951807228916E-01\n37\t8.67469879518072289156626506024096385542E-01\n38\t8.72289156626506024096385542168674698795E-01\n39\t8.72289156626506024096385542168674698795E-01\n40\t8.74698795180722891566265060240963855422E-01\n41\t8.79518072289156626506024096385542168675E-01\n42\t8.84337349397590361445783132530120481928E-01\n43\t8.89156626506024096385542168674698795181E-01\n44\t8.98795180722891566265060240963855421687E-01\n45\t9.0361445783132530120481927710843373494E-01\n46\t9.0361445783132530120481927710843373494E-01\n47\t9.0361445783132530120481927710843373494E-01\n48\t9.10843373493975903614457831325301204819E-01\n49\t9.20481927710843373493975903614457831325E-01\n50\t9.27710843373493975903614457831325301205E-01\n51\t9.30120481927710843373493975903614457831E-01\n52\t9.30120481927710843373493975903614457831E-01\n53\t9.32530120481927710843373493975903614458E-01\n54\t9.39759036144578313253012048192771084337E-01\n55\t9.4457831325301204819277108433734939759E-01\n56\t9.46987951807228915662650602409638554217E-01\n57\t9.46987951807228915662650602409638554217E-01\n58\t9.5180722891566265060240963855421686747E-01\n59\t9.5180722891566265060240963855421686747E-01\n60\t9.61445783132530120481927710843373493976E-01\n61\t9.61445783132530120481927710843373493976E-01\n62\t9.61445783132530120481927710843373493976E-01\n63\t9.63855421686746987951807228915662650602E-01\n64\t9.66265060240963855421686746987951807229E-01\n65\t9.66265060240963855421686746987951807229E-01\n66\t9.66265060240963855421686746987951807229E-01\n67\t9.71084337349397590361445783132530120482E-01\n68\t9.71084337349397590361445783132530120482E-01\n69\t9.75903614457831325301204819277108433735E-01\n70\t9.76554852221385542168674698795180722892E-01\n71\t9.77336396366716867469879518072289156627E-01\n72\t9.78117866975715373493975903614457831325E-01\n73\t9.7889941112104669879518072289156626506E-01\n74\t9.79680881730045180722891566265060240964E-01\n75\t9.80462425875376506024096385542168674699E-01\n76\t9.81243896484374987951807228915662650602E-01\n77\t9.82025367093373493975903614457831325301E-01\n78\t9.82806911238704819277108433734939759036E-01\n79\t9.83588381847703325301204819277108433735E-01\n80\t9.8436992599303465060240963855421686747E-01\n81\t9.85151396602033132530120481927710843374E-01\n82\t9.85932940747364457831325301204819277108E-01\n83\t9.86714411356362939759036144578313253012E-01\n84\t9.87495955501694265060240963855421686747E-01\n85\t9.88277426110692771084337349397590361446E-01\n86\t9.89058970256024096385542168674698795181E-01\n87\t9.8984044086502260240963855421686746988E-01\n88\t9.90621911474021084337349397590361445783E-01\n89\t9.91403455619352409638554216867469879518E-01\n90\t9.92184926228350891566265060240963855422E-01\n91\t9.92966470373682216867469879518072289157E-01\n92\t9.93747940982680722891566265060240963855E-01\n93\t9.9452948512801204819277108433734939759E-01\n94\t9.95310955737010554216867469879518072289E-01\n95\t9.96092499882341879518072289156626506024E-01\n96\t9.96873970491340361445783132530120481928E-01\n97\t9.97655514636671686746987951807228915663E-01\n98\t9.98436985245670168674698795180722891566E-01\n99\t9.99218529391001493975903614457831325301E-01\n100\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":5,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display customers >70% likely to be a responder","message":["%sql","","SELECT CUST_ID, PREDICTION PRED, ROUND(PROBABILITY,3) PROB, ROUND(COST,2) COST ","FROM APPLY_RESULT WHERE PREDICTION = 1 AND PROBABILITY > 0.7 ","ORDER BY PROBABILITY DESC","FETCH FIRST 10 ROWS ONLY;"," "],"enabled":true,"result":{"startTime":1715349912230,"interpreter":"sql.low","endTime":1715349912335,"results":[{"message":"CUST_ID\tPRED\tPROB\tCOST\n100244\t1\t1\t0\n104023\t1\t1\t0\n102198\t1\t1\t0\n101733\t1\t1\t0\n101148\t1\t1\t0\n104364\t1\t1\t0\n103529\t1\t1\t0\n101439\t1\t1\t0\n100719\t1\t1\t0\n103019\t1\t1\t0\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Select and view likely and unlikely AFFINITY_CARD responders ","message":["%sql ","","SELECT CUST_ID, PREDICTION, ROUND(PROBABILITY,2) PROB, ROUND(COST,2) COST","FROM APPLY_RESULT","WHERE PREDICTION = ${PREDICTION='1','1'|'0'} ","AND PROBABILITY > 0.7 ORDER BY PROBABILITY DESC","FETCH FIRST 10 ROWS ONLY;"," "],"enabled":true,"result":{"startTime":1715349912428,"interpreter":"sql.low","endTime":1715349912505,"results":[{"message":"CUST_ID\tPREDICTION\tPROB\tCOST\n100011\t0\t1\t0\n100907\t0\t1\t0\n100735\t0\t1\t0\n100730\t0\t1\t0\n100485\t0\t1\t0\n100417\t0\t1\t0\n100113\t0\t1\t0\n100198\t0\t1\t0\n100297\t0\t1\t0\n100364\t0\t1\t0\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":"{\"PREDICTION\":\"'0'\"}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[{\"type\":\"Select\",\"name\":\"PREDICTION\",\"displayName\":null,\"defaultValue\":\"'1'\",\"argument\":null,\"options\":[{\"value\":\"'1'\",\"displayName\":null},{\"value\":\"'0'\",\"displayName\":null}],\"isHidden\":false,\"isProgrammatic\":false}]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Dynamically select customers >70% likely to be a responder","message":["%sql","","SELECT * ","FROM (SELECT CUST_ID, PREDICTION_PROBABILITY(NN_CLASS_MODEL, '1' USING A.*) PROBABILITY"," FROM TEST_DATA_CLAS A)","WHERE PROBABILITY > 0.7","FETCH FIRST 20 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715349912586,"interpreter":"sql.low","endTime":1715349912707,"results":[{"message":"CUST_ID\tPROBABILITY\n101947\t0.9999796342228668\n100244\t1.0\n103019\t1.0\n100014\t0.9679558659866478\n100122\t0.999999993011917\n100169\t0.999556560644016\n100190\t0.9952874825916247\n100327\t0.9999617299807431\n100429\t0.9955310402632818\n100719\t1.0\n101058\t0.9999987963737135\n101237\t0.9999824254629237\n101301\t0.9999836882687749\n101306\t0.9999999999892104\n101375\t0.9981369706799981\n101439\t1.0\n101462\t0.7180581975200412\n101533\t0.9998926783560763\n101638\t0.9981369706799981\n101731\t0.9999999267793567\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Dynamically select customer >70% likely to be a responder","message":["%sql","","SELECT CUST_ID","FROM TEST_DATA_CLAS","WHERE PREDICTION_PROBABILITY(NN_CLASS_MODEL, '1' USING *) > 0.7","FETCH FIRST 20 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715349912787,"interpreter":"sql.low","endTime":1715349912909,"results":[{"message":"CUST_ID\n101947\n100244\n103019\n100014\n100122\n100169\n100190\n100327\n100429\n100719\n101058\n101237\n101301\n101306\n101375\n101439\n101462\n101533\n101638\n101731\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Apply the model to a single new record","message":["%sql","","-- Predict the probability of being a high AFFINITY_CARD responder (1) ","-- by providing specific attribute values for a customer.","","SELECT ROUND(PREDICTION_PROBABILITY(NN_CLASS_MODEL, '1' USING "," '3' AS HOUSEHOLD_SIZE, "," 5 AS YRS_RESIDENCE, "," 1 AS Y_BOX_GAMES),3) PROBABILITY_AFFINITY_CARD_RESPONDER","FROM DUAL;"," "],"enabled":true,"result":{"startTime":1715349912988,"interpreter":"sql.low","endTime":1715349913068,"results":[{"message":"PROBABILITY_AFFINITY_CARD_RESPONDER\n0\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get prediction details","message":["%sql","","SELECT CUST_ID,"," round(PREDICTION_YRS_RES,3) PRED_YRS_RES,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute1\",17,100)),'rank=\"1\"/>') FIRST_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute2\",17,100)),'rank=\"2\"/>') SECOND_ATTRIBUTE","FROM (SELECT CUST_ID,"," PREDICTION(NN_CLASS_MODEL USING *) PREDICTION_YRS_RES,"," PREDICTION_DETAILS(NN_CLASS_MODEL USING *) PD"," FROM TEST_DATA_CLAS"," WHERE CUST_ID < 100025"," ORDER BY CUST_ID) OUT,"," XMLTABLE('/Details' PASSING OUT.PD COLUMNS "," \"Attribute1\" XMLType PATH 'Attribute[1]',"," \"Attribute2\" XMLType PATH 'Attribute[2]') OUTPRED","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715349913150,"interpreter":"sql.low","endTime":1715349913280,"results":[{"message":"CUST_ID\tPRED_YRS_RES\tFIRST_ATTRIBUTE\tSECOND_ATTRIBUTE\n100004\t0\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".491\" \t\n100009\t0\t\"YRS_RESIDENCE\" actualValue=\"3\" weight=\"1\" \t\"FLAT_PANEL_MONITOR\" actualValue=\"0\" weight=\"1\" \n100011\t0\t\t\n100012\t1\t\"FLAT_PANEL_MONITOR\" actualValue=\"0\" weight=\".609\" \t\n100013\t0\t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\".682\" \t\"FLAT_PANEL_MONITOR\" actualValue=\"0\" weight=\".682\" \n100014\t1\t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\".968\" \t\"YRS_RESIDENCE\" actualValue=\"5\" weight=\".968\" \n100018\t0\t\"OS_DOC_SET_KANJI\" actualValue=\"1\" weight=\"1\" \t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## Create Classification Neural Network model using a settings table","","The settings table is an alternative way to specify algorithm settings to build the model. We first drop the settings table, create it, and then populate it with settings before building the model."],"enabled":true,"result":{"startTime":1715349913360,"interpreter":"md.low","endTime":1715349913426,"results":[{"message":"<h2 id=\"create-classification-neural-network-model-using-a-settings-table\">Create Classification Neural Network model using a settings table<\/h2>\n<p>The settings table is an alternative way to specify algorithm settings to build the model. We first drop the settings table, create it, and then populate it with settings before building the model.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Drop previous settings and output tables and create NN model settings table","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE N1_BUILD_SETTINGS PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","CREATE TABLE N1_BUILD_SETTINGS ("," setting_name VARCHAR2(30),"," setting_value VARCHAR2(4000));","/","BEGIN"," INSERT INTO N1_BUILD_SETTINGS (setting_name, setting_value) VALUES ('ALGO_NAME', 'ALGO_NEURAL_NETWORK');"," INSERT INTO N1_BUILD_SETTINGS (setting_name, setting_value) VALUES ('PREP_AUTO', 'ON');","END;"],"enabled":true,"result":{"startTime":1715349913510,"interpreter":"script.low","endTime":1715349913699,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable N1_BUILD_SETTINGS created.\n\n\n---------------------------\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":0,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build classification model","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('NN_CLASS_MODEL3');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","BEGIN"," DBMS_DATA_MINING.CREATE_MODEL('NN_CLASS_MODEL3', 'CLASSIFICATION', 'TRAIN_DATA_CLAS', 'CUST_ID',' AFFINITY_CARD', 'N1_BUILD_SETTINGS');","END;"],"enabled":true,"result":{"startTime":1715349913787,"interpreter":"script.low","endTime":1715349918396,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","## End of Script"],"enabled":true,"result":{"startTime":1715349918480,"interpreter":"md.low","endTime":1715349918547,"results":[{"message":"<h2 id=\"end-of-script\">End of Script<\/h2>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":null,"message":[],"enabled":true,"result":{"startTime":1715349918632,"interpreter":"md.low","endTime":1715349918690,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"}],"version":"6","snapshot":false,"tags":null}]